package com.luobo.service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.luobo.entity.Comment;
import com.luobo.entity.Guanka;
import com.luobo.entity.News;
import com.luobo.entity.Question;
import com.luobo.entity.Tujian;
import com.luobo.util.LuoboJdbcDaoSupport;
import com.luobo.util.PageUtil;

@Repository
public class BuluoDao extends LuoboJdbcDaoSupport{

	/**
	 * tuwen gonglve
	 * @return
	 */
	public List<Guanka> getGuanKaList(Integer page){
		
		String sql = "select id,title,icon,create_date from buluo_guanka_text  order by create_date desc";
		List<Object> params = new ArrayList<Object>();
		params.add(PageUtil.getBeginNum(page));
		params.add(PageUtil.getPageSize());
		sql = PageUtil.getPageSql(sql);
		return getJdbcTemplate().query(sql,params.toArray(),new RowMapper<Guanka>() {
				@Override
				public Guanka mapRow(ResultSet rs, int rowNum) throws SQLException {
					Guanka guanka = new Guanka();
					guanka.setId(rs.getInt("id"));
					guanka.setTitle(rs.getString("title"));
					guanka.setIcon(rs.getString("icon"));
					guanka.setDate(rs.getTimestamp("create_date"));
					return guanka;
			}
		});
	}
	
	/**
	 * 
	 * tujian列表
	 * @param game 1 2
	 * @return
	 */
	public List<Tujian> getTujianList(){
		
		String sql = "select id,type,name,icon from buluo_tujian order by type";
		
		return getJdbcTemplate().query(sql,new RowMapper<Tujian>() {
				@Override
				public Tujian mapRow(ResultSet rs, int rowNum) throws SQLException {
					Tujian tujian = new Tujian();
					tujian.setId(rs.getInt("id"));
					tujian.setType(rs.getInt("type"));
					tujian.setName(rs.getString("name"));
					tujian.setIcon(rs.getString("icon"));
					return tujian;
			}
		});
	}
	
	/**
	 * 
	 * @param game 1 2
	 * @param page 页码
	 * @return
	 */
	public List<News> getNewsList(Integer page){
		String sql = "select id,title,icon,create_date from buluo_news  order by create_date desc";
		List<Object> params = new ArrayList<Object>();
		params.add(PageUtil.getBeginNum(page));
		params.add(PageUtil.getPageSize());
		sql = PageUtil.getPageSql(sql);
		return getJdbcTemplate().query(sql, params.toArray(),new RowMapper<News>(){
			@Override
			public News mapRow(ResultSet rs, int rowNum) throws SQLException {
				News news = new News();
				news.setId(rs.getInt("id"));
				news.setTitle(rs.getString("title"));
				news.setIcon(rs.getString("icon"));
				news.setDate(rs.getTimestamp("create_date"));
				return news;
			}
		});
	}
	public List<Question> getQuestionList( Integer page) {
		String sql = "select id,title,icon,create_date from buluo_question  order by create_date desc ";
		List<Object> params = new ArrayList<Object>();
		params.add(PageUtil.getBeginNum(page));
		params.add(PageUtil.getPageSize());
		sql = PageUtil.getPageSql(sql);
		return getJdbcTemplate().query(sql, params.toArray(),new RowMapper<Question>(){
			@Override
			public Question mapRow(ResultSet rs, int rowNum) throws SQLException {
				Question question = new Question();
				question.setId(rs.getInt("id"));
				question.setTitle(rs.getString("title"));
				question.setIcon(rs.getString("icon"));
				question.setDate(rs.getTimestamp("create_date"));
				return question;
			}
		});
	}
	/**
	 * 根据id查找新闻
	 * @param id
	 * @return
	 */
	public News getNewsById(Integer id) {
		String sql = "select id,title,create_date,content from buluo_news where id = ?";
		return getJdbcTemplate().queryForObject(sql, new Object[]{id},new RowMapper<News>() {
			@Override
			public News mapRow(ResultSet rs, int rowNum) throws SQLException {
				News news = new News();
				news.setId(rs.getInt("id"));
				news.setTitle(rs.getString("title"));
				news.setContent(rs.getString("content"));
				news.setDate(rs.getDate("create_date"));
				return news;
			}
		});
	}
	/**
	 * 根据id查找问题
	 * @param id
	 * @return
	 */
	public Question getQuestionById(Integer id) {
		String sql = "select id,title,create_date,content from buluo_question where id = ?";
		return getJdbcTemplate().queryForObject(sql, new Object[]{id},new RowMapper<Question>() {
			@Override
			public Question mapRow(ResultSet rs, int rowNum) throws SQLException {
				Question question = new Question();
				question.setId(rs.getInt("id"));
				question.setTitle(rs.getString("title"));
				question.setContent(rs.getString("content"));
				question.setDate(rs.getDate("create_date"));
				return question;
			}
		});
	}
	/**
	 * 根据id查找攻略
	 * @param id
	 * @return
	 */
	public Guanka getGonglveById(Integer id) {

		String sql = "select id,title,content,create_date from buluo_guanka_text where id = ? ";
		
		return getJdbcTemplate().queryForObject(sql, new Object[]{id},new RowMapper<Guanka>() {
			@Override
			public Guanka mapRow(ResultSet rs, int rowNum) throws SQLException {
				Guanka guanka = new Guanka();
				guanka.setId(rs.getInt("id"));
				guanka.setTitle(rs.getString("title"));
				guanka.setContent(rs.getString("content"));
				guanka.setDate(rs.getDate("create_date"));
				return guanka;
			}
		});
	}
	
	/**
	 * 根据id查找攻略
	 * @param id
	 * @return
	 */
	public Guanka getVideoById(Integer id) {

		String sql = "select id,title,content,create_date,video_url from buluo_guanka_video where id = ? ";
		
		return getJdbcTemplate().queryForObject(sql, new Object[]{id},new RowMapper<Guanka>() {
			@Override
			public Guanka mapRow(ResultSet rs, int rowNum) throws SQLException {
				Guanka guanka = new Guanka();
				guanka.setId(rs.getInt("id"));
				guanka.setTitle(rs.getString("title"));
				guanka.setContent(rs.getString("content"));
				guanka.setDate(rs.getDate("create_date"));
				guanka.setVideoUrl(rs.getString("video_url"));
				return guanka;
			}
		});
	}
	
	/**
	 * 根据id查找Tujian
	 * @param id
	 * @return
	 */
	public Tujian getTujianById(Integer id) {
		String sql = "select id,title,create_date,content from buluo_tujian where id = ?";
		return getJdbcTemplate().queryForObject(sql, new Object[]{id},new RowMapper<Tujian>() {
			@Override
			public Tujian mapRow(ResultSet rs, int rowNum) throws SQLException {
				Tujian tujian = new Tujian();
				tujian.setId(rs.getInt("id"));
				tujian.setTitle(rs.getString("title"));
				tujian.setContent(rs.getString("content"));
				tujian.setDate(rs.getDate("create_date"));
				return tujian;
			}
		});
	}

	/**
	 * 保存评论
	 * @param comment
	 */
	public void saveComment(Comment comment) {
		String sql = "insert into buluo_comment(name,type,content,create_date,relate_id) values(?,?,?,now(),?)";
		getJdbcTemplate().update(sql,new Object[]{comment.getName(),comment.getType(),comment.getContent(),comment.getRelateId()});
	}

	/**
	 * 得到评论列表
	 * @param id
	 * @return
	 */
	public List<Comment> getCommentList(Integer id) {
		String sql = "select id,name,content,create_date from buluo_comment  where relate_id = ? order by create_date desc";
		return getJdbcTemplate().query(sql, new Object[]{id},new RowMapper<Comment>() {
			@Override
			public Comment mapRow(ResultSet rs, int rowNum) throws SQLException {
				Comment comment = new Comment();
				comment.setId(rs.getInt("id"));
				comment.setName(rs.getString("name"));
				comment.setContent(rs.getString("content"));
				comment.setCreateDate(rs.getTimestamp("create_date"));
				return comment;
			}
		});
	}

	/**
	 * video list
	 * @param page
	 * @return
	 */
	public List<Guanka> getVideoList(Integer page) {
		String sql = "select id,title,icon,create_date from buluo_guanka_video  order by create_date desc";
		List<Object> params = new ArrayList<Object>();
		params.add(PageUtil.getBeginNum(page));
		params.add(PageUtil.getPageSize());
		sql = PageUtil.getPageSql(sql);
		return getJdbcTemplate().query(sql,params.toArray(),new RowMapper<Guanka>() {
				@Override
				public Guanka mapRow(ResultSet rs, int rowNum) throws SQLException {
					Guanka guanka = new Guanka();
					guanka.setId(rs.getInt("id"));
					guanka.setTitle(rs.getString("title"));
					guanka.setIcon(rs.getString("icon"));
					guanka.setDate(rs.getTimestamp("create_date"));
					return guanka;
			}
		});
	}

}
